package data.dao.daoImplementation;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import data.dao.*;
import data.dto.*;
import data.searchdto.*;

public class CompensationConsumedDAOImpl implements CompensationConsumedDAO {

	private ArrayList<CompensationLeaveUserDetails> compensationLeaveUserDetailsList;
	private ArrayList<CompensationConsumedDTO> compensationConsumedDetialList;

	@Override
	public ArrayList<CompensationLeaveUserDetails> findCompensationLeave(
			CompensationConsumedSearchDTO compensationconsumedSearchDTO)
			throws NotFoundException, LeaveDAOException, SQLException {

		String sql = "SELECT C.userID,U.Username,C.numberofdays FROM compensationconsumed C, user U"
				+ " WHERE userID = ? AND C.userID = U.id AND isactive = 1 ";
		PreparedStatement stmt = null;
		ArrayList<CompensationLeaveUserDetails> list = null;
		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, compensationconsumedSearchDTO.getUserid());

			list = constructObjects(stmt);

		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}
	
	
	@Override
	public ArrayList<CompensationLeaveUserDetails> findAllCompensationLeave()
			throws LeaveDAOException, SQLException {

		String sql = "SELECT C.userID,U.Username,C.numberofdays FROM compensationconsumed C, user U"
				+ " WHERE C.userID = U.id AND isactive = 1 ";
		PreparedStatement stmt = null;
		ArrayList<CompensationLeaveUserDetails> list = null;
		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			list = constructObjects(stmt);

		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}

	@Override
	public void insertCompensationLeave(
			CompensationConsumedDTO compensationconsumedDTO)
			throws LeaveDAOException, SQLException {

		String sql = "";
		PreparedStatement stmt = null;

		try {
			sql = "INSERT INTO compensationconsumed ( userID, numberofDays, "
					+ " createdBy, "
					+ "modifiedBy, status, isActive) VALUES (?, ?, ?, ?, ?, ?) ";
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			stmt.setInt(1, compensationconsumedDTO.getUserid());
			stmt.setFloat(2, compensationconsumedDTO.getNumberofdays());
			//stmt.setTimestamp(3, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(3, compensationconsumedDTO.getUserid());
			//stmt.setTimestamp(5, util.CurrentDate.getCurrentTimeStamp());
			stmt.setInt(4, compensationconsumedDTO.getUserid());
			stmt.setInt(5, 1);
			stmt.setInt(6, 1);

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void updateCompensationLeave(
			CompensationConsumedDTO compensationconsumedDTO)
			throws LeaveDAOException, SQLException, NotFoundException {

		String sql = "UPDATE compensationconsumed SET numberofDays = ?, "
				+ " modifiedBy = ?, "
				+ "status = ?  WHERE id = ? AND userID = ? ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);

			stmt.setFloat(1, compensationconsumedDTO.getNumberofdays());
			stmt.setInt(2, compensationconsumedDTO.getUserid());
			stmt.setInt(3, compensationconsumedDTO.getStatus());

			stmt.setInt(4, compensationconsumedDTO.getId());
			stmt.setInt(5, compensationconsumedDTO.getUserid());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}

	@Override
	public void deleteCompensationLeave(
			CompensationConsumedDTO compensationconsumedDTO)
			throws LeaveDAOException, SQLException, NotFoundException {

		String sql = "UPDATE compensationsonsumed SET isactive = 0 WHERE (id = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, compensationconsumedDTO.getId());

			int rowcount = util.Database.getInstance().executeUpdate(stmt);
			;
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}

	}
	protected ArrayList<CompensationLeaveUserDetails> constructObjects(
			PreparedStatement stmt) throws NotFoundException,
			java.sql.SQLException {

		ResultSet result = null;
		try {
			result = util.Database.getInstance().executeQuery(stmt);
			compensationLeaveUserDetailsList = new ArrayList<CompensationLeaveUserDetails>();
			while (result.next()) {
				CompensationLeaveUserDetails valueObject = new CompensationLeaveUserDetails();
				valueObject.setUserid(result.getInt("userID"));
				valueObject.setNumberofdays(result.getInt("numberofDays"));
				valueObject.setUsername(result.getString("numberofDays"));
				compensationLeaveUserDetailsList.add(valueObject);
			}
		} catch (Exception e) {

		} finally {
			if (result != null)
				result.close();
		}
		return compensationLeaveUserDetailsList;
	}

	//Added By Thazin Win
	@Override
	public ArrayList<CompensationConsumedDTO> findCompensationDetailByID(CompensationConsumedDTO compensationConsumedDTO)
	throws LeaveDAOException, SQLException, NotFoundException{

		String sql = "SELECT * FROM compensationconsumed C"
				+ " WHERE C.userID = ? AND isactive = 1 ";
		PreparedStatement stmt = null;
		ArrayList<CompensationConsumedDTO> list = null;
		try {
			stmt = util.Database.getInstance().getConnection()
					.prepareStatement(sql);
			stmt.setInt(1, compensationConsumedDTO.getUserid());
			
			list = constructCompensationObjects(stmt);

		} catch (NotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}

	protected ArrayList<CompensationConsumedDTO> constructCompensationObjects(
			PreparedStatement stmt) throws NotFoundException,
			java.sql.SQLException {

		ResultSet result = null;
		try {
			result = util.Database.getInstance().executeQuery(stmt);
			compensationConsumedDetialList = new ArrayList<CompensationConsumedDTO>();
			while (result.next()) {
				CompensationConsumedDTO valueObject = new CompensationConsumedDTO();
				valueObject.setId(result.getInt("id"));
				valueObject.setUserid(result.getInt("userID"));
				valueObject.setNumberofdays(result.getInt("numberofDays"));
				//valueObject.setUsername(result.getString("numberofDays"));
				compensationConsumedDetialList.add(valueObject);
			}
		} catch (Exception e) {

		} finally {
			if (result != null)
				result.close();
		}
		return compensationConsumedDetialList;
	}
	

	
}
